package com.atguigu.gmall.mapper;

import com.atguigu.gmall.bean.ProductStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

public interface ProductStatsMapper {

    @Select("select sum(order_amount) order_amount from product_stats_0819 where toYYYYMMDD(stt)=#{date}")
    public BigDecimal selectGMV(Integer date);

    @Select("select tm_id, tm_name, sum(order_amount) order_amount " +
            "from product_stats_0819 where toYYYYMMDD(stt)=#{date} " +
            "group by tm_id,tm_name having order_amount>0 " +
            "order by order_amount desc limit #{limit}")
    public List<ProductStats> selectTM(@Param("date") Integer date,
                                       @Param("limit") Integer limit);

    @Select("select category3_id, category3_name, sum(order_amount) order_amount " +
            " from product_stats_0819 where toYYYYMMDD(stt)=#{date} " +
            " group by category3_id, category3_name having order_amount>0 " +
            " order by order_amount desc limit #{limit}")
    public List<ProductStats> selectCategory3(@Param("date") Integer date,
                                              @Param("limit") Integer limit);

    @Select("select spu_id, spu_name, sum(order_amount) order_amount, count(*) order_ct " +
            " from product_stats_0819 where toYYYYMMDD(stt)=#{date} " +
            " group by spu_id, spu_name " +
            " having order_amount>0 " +
            " order by order_amount desc limit #{limit}")
    public List<ProductStats> selectSpu(@Param("date") Integer date,
                                        @Param("limit") Integer limit);

}
